package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.*;

public class IndustrySourceListingDAO{

	Connection con;

	public IndustrySourceListingDAO(Connection connection){
		this.con = connection;
	}

	public int addIndustrySourceListing(IndustrySourceListingBean bean)throws SQLException{
		Statement stmt = con.createStatement();
   		ResultSet rs = stmt.executeQuery("SELECT COALESCE(MAX(ISL_SOURCE_ID),0) + 1 FROM NSD.INDUSTRY_SOURCE_LISTINGS");
		rs.next();
		int newID = rs.getInt(1);
		rs.close();
		stmt.close();

		String sql = "INSERT INTO INDUSTRY_SOURCE_LISTINGS(ISL_SOURCE_ID,ISL_REPORT_NUMBER,ISL_URL,ISL_NOTES,ISL_ASSOCIATION_INDICATOR,ISL_QUALITY_SCORE,ISL_DATE_LAST_CHECKED) VALUES(?,?,?,?," + bean.isAssociationIndicator() + ",?,?)";

		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,newID);
		pstmt.setInt(2,bean.getReportNumber());
		pstmt.setString(3,bean.getUrl());
		pstmt.setString(4,bean.getNotes());
		pstmt.setInt(5,bean.getQualityScore());
		pstmt.setDate(6,new java.sql.Date(bean.getDateLastChecked().getTime()));

		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return newID;
	}

	public int updateIndustrySourceListing(IndustrySourceListingBean bean)throws SQLException{

		String sql = "UPDATE INDUSTRY_SOURCE_LISTINGS SET ISL_REPORT_NUMBER = ?,ISL_URL = ?,ISL_NOTES = ?,ISL_ASSOCIATION_INDICATOR = " + bean.isAssociationIndicator() + ",ISL_QUALITY_SCORE = ?, ISL_DATE_LAST_CHECKED = ? WHERE ISL_SOURCE_ID = ? ";

		PreparedStatement pstmt = con.prepareStatement(sql);

		pstmt.setInt(1,bean.getReportNumber());
		pstmt.setString(2,bean.getUrl());
		pstmt.setString(3,bean.getNotes());
		pstmt.setInt(4,bean.getQualityScore());
		pstmt.setDate(5,new java.sql.Date(bean.getDateLastChecked().getTime()));
		pstmt.setInt(6,bean.getSourceId());

		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return updateCols;
	}

	public List getIndustrySourceListings(int reportId,String orderBy)throws SQLException{
		String sql = "SELECT ISL_SOURCE_ID,ISL_REPORT_NUMBER,ISL_URL,ISL_NOTES,ISL_ASSOCIATION_INDICATOR,ISL_QUALITY_SCORE,ISL_DATE_LAST_CHECKED FROM NSD.INDUSTRY_SOURCE_LISTINGS WHERE ISL_REPORT_NUMBER = ? " ;
		if(orderBy != null){
			sql += " ORDER BY " + orderBy;
		}

		System.out.println(sql + " using " + reportId);
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,reportId);
		ResultSet rs = pstmt.executeQuery();

		List result = new ArrayList();

		while(rs.next()){
			IndustrySourceListingBean bean = new IndustrySourceListingBean();
			bean.setSourceId(rs.getInt("ISL_SOURCE_ID"));
			bean.setReportNumber(rs.getInt("ISL_REPORT_NUMBER"));
			bean.setUrl(rs.getString("ISL_URL"));
			bean.setNotes(rs.getString("ISL_NOTES"));
			bean.setAssociationIndicator(rs.getBoolean("ISL_ASSOCIATION_INDICATOR"));
			bean.setQualityScore(rs.getInt("ISL_QUALITY_SCORE"));
			bean.setDateLastChecked(new java.util.Date(rs.getDate("ISL_DATE_LAST_CHECKED").getTime()));

        	result.add(bean);
		}

		rs.close();
		pstmt.close();

		return result;
	}

	public int deleteIndustrySourceListing(int sourceId)throws SQLException{
		String sql = "DELETE FROM NSD.INDUSTRY_SOURCE_LISTINGS WHERE ISL_SOURCE_ID = ? " ;

		System.out.println(sql + " using " + sourceId);
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,sourceId);
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return updateCols;
	}

}